﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CommonLibrary.DataModel.WorkFlow;
using System.Data.SqlClient;
using CommonLibrary.DB;
using CommonLibrary.Common;
using System.Data;

namespace ServiceApplication.Dao.WorkFlow
{
    public class WfInsDao
    {
        public void addIns(WfInsEntity wfinsEntity)
        {
            string sql = "INSERT INTO wf_ins ( [uniqueid] ,[wf_id] ,[user_id] ,[insname] ,[createdate] ,[lastdate] ,[flow_state] ,[result],[sqlargs],[ins_id],[clr_id],[ins_care],[ins_appendix],[ins_descn] ) " +
                "VALUES (@uniqueid,@wf_id,@user_id,@insname,@createdate,@lastdate,@flow_state,@result,@sqlargs,@ins_id,@clr_id,@ins_care,@ins_appendix,@ins_descn)";
            SqlParameter[] sqlParams = new SqlParameter[]{
                new SqlParameter("@uniqueid",wfinsEntity.uniqueid),
                new SqlParameter("@wf_id",wfinsEntity.wf_id),
                new SqlParameter("@user_id",wfinsEntity.user_id),
                new SqlParameter("@insname",wfinsEntity.insname),
                new SqlParameter("@createdate",wfinsEntity.createdate),
                new SqlParameter("@lastdate",wfinsEntity.lastdate),
                new SqlParameter("@flow_state",wfinsEntity.flow_state),
                new SqlParameter("@result",wfinsEntity.result),
                new SqlParameter("@sqlargs",wfinsEntity.sqlargs),
                new SqlParameter("@ins_id",wfinsEntity.ins_id),
                new SqlParameter("@clr_id",wfinsEntity.clr_id),
                new SqlParameter("@ins_care",wfinsEntity.ins_care),
                new SqlParameter("@ins_appendix",wfinsEntity.ins_appendix),
                new SqlParameter("@ins_descn",wfinsEntity.ins_descn),
            };
            DBExecuteUtil.executeSqlWithPar(sql, sqlParams);
        }

        public void updateInsResult(string uniqueid, string result)
        {
            string sql = "update wf_ins set result=@result where uniqueid=@uniqueid;";
            SqlParameter[] sqlParams = new SqlParameter[] { 
                new SqlParameter("@uniqueid", uniqueid) ,
                
                new SqlParameter("@result", result) 
            };
            DBExecuteUtil.executeSqlWithPar(sql, sqlParams);
        }

        /// <summary>
        ///  返回结果按照是否完成，发起日期分类
        /// </summary>
        /// <param name="user_id">发起人id</param>
        public DataTable queryIns(string user_id)
        {
            string sql = " select ins_id 流水编码,ins.uniqueid 唯一标识,wf_id 流程编码,ins.user_id 发起人编码,users.user_name 发起人,dutys.duty_name 职位,depts.dept_name 部门,insname 名称, " +
                           "   createdate 发起日期,lastdate 处理日期,flow_state 流程状态,result 结果,sqlargs 数据源参数,  " +
                           "   ins_care 关注,ins_descn 备注,taskname 当前步骤,task.task_id 任务编码 " +
                           "   from wf_ins ins,wf_task task,jc_user users,jc_staff staffs,jc_dept depts,jc_duty dutys  " +
                           "   where ins.uniqueid=task.uniqueid and ins.user_id=users.user_id and users.staff_id=staffs.staff_id " +
                           "   and staffs.duty_id=dutys.duty_id and staffs.dept_id=depts.dept_id and ins.user_id=@user_id     " +
                           "   and task.task_id=(select max(tmp.task_id) from wf_task tmp where tmp.uniqueid=task.uniqueid) and task.state in ('已处理','未处理','开始')  " +
                           " order by createdate desc ";

            SqlParameter[] sqlParams = new SqlParameter[] { 
                new SqlParameter("@user_id", user_id) 
            };
            DataTable dt = DBExecuteUtil.querySqlTable(sql, sqlParams);
            return dt;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="user_id"></param>
        /// <param name="applyer"></param>
        /// <param name="wfname"></param>
        /// <param name="stime"></param>
        /// <param name="etime"></param>
        /// <param name="state">例如： " '处理中','未处理' "</param>
        /// <returns></returns>
        public DataTable queryInsByCondition(string user_id, String applyer, String wfname, String stime, String etime, String state)
        {
            string sql = " select ins_id 流水编码,ins.uniqueid 唯一标识,wf_id 流程编码,ins.user_id 发起人编码,users.user_name 发起人,insname 名称, " +
                 " createdate 发起日期,lastdate 处理日期,flow_state 流程状态,result 结果,sqlargs 数据源参数, " +
                 " ins_care 关注,ins_descn 备注,taskname 当前步骤,task.user_id 处理人编码,users2.user_name 处理人,isread 已读,task.task_id 任务编码 from wf_ins ins,wf_task task,jc_user users,jc_user users2  " +
                 " where ins.uniqueid=task.uniqueid and ins.user_id=users.user_id and task.user_id=users2.user_id and ins.user_id=@user_id    " +
                 " and task.task_id=(select max(tmp.task_id) from wf_task tmp where tmp.uniqueid=task.uniqueid)  ";

            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@user_id", user_id));
            //if (uniqueids != null && uniqueids.Length > 0)
            //{
            //    sql += "  and task.uniqueid in (";
            //    for (int i = 0; i < uniqueids.Length; i++)
            //    {
            //        sql += "'" + uniqueids[i] + "',";
            //    }
            //    sql = sql.Substring(0, sql.Length - 1);//去除最后一个逗号
            //    sql += ") ";
            //}
            if (!String.IsNullOrEmpty(state))
            {
                sql += " and task.state in (" + state + ") ";
            }
            if (!String.IsNullOrEmpty(wfname))
            {
                sql += " and ins.insname =@wfname ";
                parameterList.Add(new SqlParameter("@wfname", wfname));
            }
            if (!String.IsNullOrEmpty(applyer))
            {
                sql += " and users.name like '%' + @applyer + '%'";
                parameterList.Add(new SqlParameter("@applyer", applyer));
            }
            if (!String.IsNullOrEmpty(stime))
            {
                stime = DateUtil.getBeginTimeBySecond(DateTime.Parse(stime));
                sql += " and task.starttime>=@stime ";
                parameterList.Add(new SqlParameter("@stime", stime));
            }
            if (!String.IsNullOrEmpty(etime))
            {
                etime = DateUtil.getEndTimeBySecond(DateTime.Parse(etime));
                sql += " and task.starttime<=@etime ";
                parameterList.Add(new SqlParameter("@etime", etime));
            }
            sql += " order by createdate desc ";
            DataTable dt = DBExecuteUtil.querySqlTable(sql, parameterList.ToArray());
            return dt;
        }

        public WfInsEntity queryInsEntity(string uniqueid)
        {
            WfInsEntity wfinsEntity = null;
            string sql = "select uniqueid,wf_id,insname,user_id,createdate,lastdate,flow_state,result,sqlargs,ins_id,clr_id,ins_care,ins_appendix,ins_descn from wf_ins where uniqueid=@uniqueid ";
            SqlParameter[] sqlParams = new SqlParameter[] { 
                new SqlParameter("@uniqueid", uniqueid) 
            };
            var result = DBExecuteUtil.querySqlArray(sql, sqlParams);
            if (result != null && result.Length > 0)
            {
                wfinsEntity = new WfInsEntity()
                {
                    uniqueid = new Guid(result[0][0]),
                    wf_id = int.Parse(result[0][1]),
                    insname = result[0][2],
                    user_id = int.Parse(result[0][3]),
                    createdate = result[0][4],
                    lastdate = result[0][5],
                    flow_state = int.Parse(result[0][6]),
                    result = result[0][7],
                    sqlargs = result[0][8],
                    ins_id = result[0][9],
                    clr_id = result[0][10],
                    ins_care = int.Parse(result[0][11]),
                    ins_appendix = result[0][12].ToString(),
                    ins_descn = result[0][13].ToString(),
                };
            }
            return wfinsEntity;
        }

        /// <summary>
        /// distinct ins_id 
        /// </summary>
        /// <param name="user_id">发起人</param>
        /// <param name="wftype_name"></param>
        /// <returns></returns>
        public DataTable queryPlan(string creater_id, string wftype_name)
        {
            string sql = "  select distinct ins_id 流水编码,user_id 用户编码,insname 名称,createdate 发起日期 from wf_ins ins,wf_wf wf,wf_wftype wftype  " +
                             "  where ins.wf_id=wf.wf_id and wf.wftype_id=wftype.wftype_id  and user_id=@user_id and wftype_name=@wftype_name ";
            SqlParameter[] sqlParams = new SqlParameter[] { 
                new SqlParameter("@user_id", creater_id) ,
                new SqlParameter("@wftype_name", wftype_name) ,
            };
            DataTable dt = DBExecuteUtil.querySqlTable(sql, sqlParams);
            return dt;
        }

        public DataTable queryPlanByInsid(string ins_id)
        {
            string sql = "  select uniqueid 唯一标识,insname 名称,name 处理人,createdate 发起日期,lastdate 处理日期,flow_state 状态,result 结果 " +
                             "  from wf_ins ins,jc_user users where ins.clr_id=users.user_id and ins_id=@ins_id  ";
            SqlParameter[] sqlParams = new SqlParameter[] { 
                new SqlParameter("@ins_id", ins_id) ,
            };
            DataTable dt = DBExecuteUtil.querySqlTable(sql, sqlParams);
            return dt;
        }

        // 根据唯一编号获得 参数值
        public String getInsargs(String uniqueid)
        {
            String sql = "SELECT [sqlargs] FROM wf_ins WHERE [uniqueid] = @uniqueid";
            SqlParameter[] sqlParams = new SqlParameter[]{
                new SqlParameter("@uniqueid",uniqueid)
            };
            var args = DBExecuteUtil.getUniqueResult(sql, sqlParams.ToArray());

            return args;
        }

        public void PersistenceInstanceClear()
        {
            string sql = @" use master
                                delete from SqlPersistenceService.dbo.InstanceState
                                where uidInstanceID not in (select uniqueid from OAonline.dbo.wf_ins where result='未完成' ) ";
            DBExecuteUtil.executeSql(sql);
        }
    }
}
